Database - CRUD operation
We have to first install mysql.connector module to work with MySQL database.
Use command - pip install mysql-connector-python to install mysql driver.
- import mysql.connector module
- make connection with database using following command
mydb = mysql.connector.connect( host="localhost", user="username", password="password" )
- Then create cursor object: cursor = mydb.cursor()
- With cursor object we can execute SQL statements: cursor.execute(sql_stmt)
Here is a full menu driver program to perform CRUD operation on MySQL database
import mysql.connector
if __name__ == "__main__":
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="",
database="sawadee",
)
mycursor = mydb.cursor()
while True:
print("1.Enter Record\n2.Show Records\n3. Update Records\n4.Delete record\n5 Exit")
choice = int(input("Enter your choice: "))
if choice == 1:
cname = input("Enter your name: ")
mobile = input("Enter your mobile: ")
address = input("Enter your address: ")
sql = "insert into customers(cname,mobile,address) values(%s,%s,%s)"
val = (cname, mobile, address)
mycursor.execute(sql, val)
mydb.commit()
elif choice == 2:
sql = "select * from customers"
mycursor.execute(sql)
records = mycursor.fetchall()
for x in records:
print(x)
elif choice == 3:
print("Enter name to update address")
name = input("Enter your name: ")
print("Enter new address")
address = input("Enter new address: ")
sql = "update customers set address = %s where cname = %s"
val = (address, name)
mycursor.execute(sql, val)
mydb.commit()
elif choice == 4:
print("Enter name to delete")
name = input("Enter your name: ")
sql = "delete from customers where cname = %s"
val = (name, )
mycursor.execute(sql, val)
mydb.commit()
else:
exit()